﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO

CREATE TABLE [dbo].[bht_NemoTranslates](
	[trans_id] [int] IDENTITY(1,1) NOT NULL,
	[record_id] [int] NOT NULL,
	[field_id] [int] NOT NULL,
	[translate] [nvarchar](max) NOT NULL,
	[lang_code] [nchar](5) NULL,
	[mtime] [datetime] NOT NULL,
	[muser] [int] NOT NULL,
 CONSTRAINT [PK_bht_NemoTranslates] PRIMARY KEY CLUSTERED 
(
	[trans_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[bht_NemoTranslates] ADD  CONSTRAINT [DF_bht_NemoTranslates_ctime]  DEFAULT (getdate()) FOR [mtime]

GO

CREATE PROCEDURE [dbo].[bhp_NemoTranslateSet] (
	@FieldId		INT,
	@Record		INT,
	@Language	NCHAR(5),
	@User			INT,
	@Translate	NVARCHAR(MAX)
)
AS
BEGIN

	SET XACT_ABORT ON;

	BEGIN TRAN

		IF EXISTS (
			SELECT 
				1
			FROM 
				[bht_NemoTranslates] t
			WHERE
				record_id		= @Record
				AND field_id	= @FieldId
				AND lang_code	= @Language
		)
			UPDATE
				[bht_NemoTranslates]
			SET
				translate		= @Translate,
				mtime			= GETDATE(),
				muser			= @User
			WHERE
				record_id		= @Record
				AND field_id	= @FieldId
				AND lang_code	= @Language
		ELSE
			INSERT INTO [bht_NemoTranslates] (record_id, field_id, translate, lang_code, muser)
					VALUES (@Record, @FieldId, @Translate, @Language, @User)
		
	COMMIT


END
GO





